CREATE VIEW [dbo].[vGroupReport]
AS
SELECT     G.Description AS GroupDescription, G.IsSystem, G.IsAutoGenerated, G.Priority, G.IsInvitationOnly, GSR.GroupStatusDesc, G.IsSimpleGroup,
                      G.InheritRolesFlag, G.IsSingleRole, GM.JoinDate, GM.DropDate, GMD.GroupMemberStatusCode, GMSR.GroupMemberStatusDesc, GMD.EffectiveDate,
                      GMD.ExpirationDate, GMD.IsActive, GMD.TypeSpecificData, GRR.GroupRoleDesc, G.GroupKey, GM.GroupMemberKey, GM.MemberContactKey,
                      G.SystemEntityKey, G.Name AS GroupName, GTR.GroupTypeName
FROM         dbo.GroupMain AS G LEFT OUTER JOIN
                      dbo.GroupMember AS GM ON G.GroupKey = GM.GroupKey LEFT OUTER JOIN
                      dbo.GroupMemberDetail AS GMD ON GM.GroupMemberKey = GMD.GroupMemberKey INNER JOIN
                      dbo.GroupTypeRef AS GTR ON G.GroupTypeKey = GTR.GroupTypeKey LEFT OUTER JOIN
                      dbo.GroupRoleRef AS GRR ON GMD.GroupRoleKey = GRR.GroupRoleKey LEFT OUTER JOIN
                      dbo.GroupStatusRef AS GSR ON GSR.GroupStatusCode = G.GroupStatusCode LEFT OUTER JOIN
                      dbo.GroupMemberStatusRef AS GMSR ON GMD.GroupMemberStatusCode = GMSR.GroupMemberStatusCode

GO